import java.sql.*;

import javax.sql.*;

import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.BufferedWriter;
import java.io.InputStreamReader;

import java.io.IOException;

import java.util.regex.*;

import java.lang.*;
import java.util.*;
import java.util.Date;

import org.apache.commons.lang.StringUtils;

import org.apache.commons.lang.StringEscapeUtils;


public class GetBugStatistics 
{
	//the file to store extracted filepath suffix
	String bugDstFile = null;
	
	//db object
	DB db = null;
	
	//threshold timestamp, used to indicate whether the time is null or not
	Timestamp thresholdTime = null;
	
	//the number of rows fetched each time
	public final static int BUGBODY_ROW_LIMIT= 1000;
	public final static int DESCRIPTION_ROW_LIMIT = 500;
	public final static int BUGID_ROW_LIMIT = 10000;
	public final static int COMMENT_ROW_LIMIT = 500;
	
	//store distributed map
	private HashMap<Integer, Integer> distributionMap = null;
	
	//store bugid
	private HashSet<Integer> bugidSet = null;
	
	//store file suffices
	private HashSet<Integer> fileReferencedSet = null;
	
	//store descriptions
	private HashSet<Integer> bugidReferencedSet = null;
	
	public GetBugStatistics() throws ClassNotFoundException, 
		IllegalAccessException, SQLException
	{
		//get the filepath
		this.bugDstFile = bugDstFile;
		
		//connect to the db
		db = new DB();
		db.connect();
		
		//set up threshold timestamp
	
		//set up a timezone
		SimpleTimeZone tZone = new SimpleTimeZone(0, "");		
		
		Calendar dayTime = Calendar.getInstance();
		
		dayTime.setTimeZone(tZone);
		
		//set up the threshold time as 2012-Jan-20
		dayTime.set(2012, 1, 20, 0, 0, 0);
		
		//convert it into millisec since the GMT time and set up the timestamp
		thresholdTime = new Timestamp(dayTime.getTime().getTime());
	}
	
	//add current size into the distribution map
	void addMap(int size)
	{
		//if this size exists, just increase it by 1
		if (distributionMap.containsKey(size))
		{
			int num = distributionMap.get(size);
			
			//need to remove it first, inefficient :(
			distributionMap.remove(size);
			
			distributionMap.put(size, num + 1);
		}
		//else add it into this map, set the num as 1
		else
		{
			distributionMap.put(size, 1);
		}
	}
	
	//check whether this timestamp is newer than the threshold time
	private boolean isTimeNull(Timestamp time)
	{
		return (time.getTime() - thresholdTime.getTime() > 0);
	}
	
	private void getBugSubEntityNum(int rowNumLimit, String bugDstFile) 
			throws SQLException, IOException
	{
		this.bugDstFile = bugDstFile;
		
		//instantiate the distribution hash map
		distributionMap = new HashMap<Integer, Integer>();
		
		//sql statement that counts the number of rows in comment given bugid
		String countCommentStatement = "select count(*) from bugBody where bugid = ?";
		
		//sql statement that retrieves rows in bugBody
		String countBugBodyStatement = "select * from bugBody";
		
		//prepared statement object for comment
		PreparedStatement countCommentps = null;
		
		//prepared statement object for bugBody
		PreparedStatement countBugBodyps = null;
		
		try
		{
			//get the prepared statement
			countCommentps = db.getStreamPreparedStatement(countCommentStatement, 
					rowNumLimit);
			
			countBugBodyps = db.getStreamPreparedStatement(countBugBodyStatement, 
					rowNumLimit);
			
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		
		
		int count = 0;
		int totalEntityNum = 0;
				
		//retrive bugBody
		ResultSet bugBodyRs = countBugBodyps.executeQuery();
		while (bugBodyRs.next())
		{
			int entityNum = 0;
			
			int bugid = bugBodyRs.getInt(1);
			//cant be 0
			if (bugid == 0)
			{
				throw new RuntimeException();
			}

			entityNum++;

			
			//check if entity title exists
			String title = bugBodyRs.getString(2);
			if (title != null)
			{
				entityNum++;
			}
			
			//check if entity status exists
			String status = bugBodyRs.getString(3);
			if (status != null)
			{
				entityNum++;
			}
			
			//check if entity owner exists
			String owner = bugBodyRs.getString(4);
			if (owner != null)
			{
				entityNum++;
			}
			
			//check if entity closedOn exists
			Timestamp closedOn = bugBodyRs.getTimestamp(5);
			if (!isTimeNull(closedOn))
			{
				entityNum++;
			}
			
			//check if entity type exists
			String type = bugBodyRs.getString(6);
			if (type != null)
			{
				entityNum++;
			}
			
			//check if entity priority exists
			String priority = bugBodyRs.getString(7);
			if (priority != null)
			{
				entityNum++;
			}
			
			//check if entity component exists
			String component = bugBodyRs.getString(8);
			if (component != null)
			{
				entityNum++;
			}

			//check if entity stars exists
			int stars = bugBodyRs.getInt(9);
			if (stars != 0)
			{
				entityNum++;
			}
			
			//check if entity reportedBy exists
			String reportedBy = bugBodyRs.getString(10);
			if (reportedBy != null)
			{
				entityNum++;
			}
			
			//check if entity openedDate exists
			Timestamp openedDate = bugBodyRs.getTimestamp(11);
			if (!isTimeNull(openedDate))
			{
				entityNum++;
			}
			
			//set up the bugid for selection
			countCommentps.setInt(1, bugid);
						
			//get the number of comments
			ResultSet commentRs = countCommentps.executeQuery();
			entityNum += commentRs.getFetchSize();
			
			//for description, it must not be void
			entityNum++;
			
			//add it into the distribution map
			addMap(entityNum);
			
			count++;
			totalEntityNum += entityNum;
		}
		
		System.out.println("bug entities num --> " + totalEntityNum);
		
		writeDistributionToFile(bugDstFile);
		
		bugDstFile = null;
	}
	
	private void getDescriptionSize(int rowNumLimit, String bugDstFile) 
			throws SQLException, IOException
	{
		//instantiate the distribution hash map
		distributionMap = new HashMap<Integer, Integer>();
		
		//sql statement that counts the number of rows in comment given bugid
		String countDescriptionStatement = 
				"select description from description where description is not null";
		
		//prepared statement object for comment
		PreparedStatement countDescriptionps = null;
		
		try
		{
			//get the prepared statement
			countDescriptionps = db.getStreamPreparedStatement(countDescriptionStatement, 
					rowNumLimit);			
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		
		//retrive description
		ResultSet descriptionRs = countDescriptionps.executeQuery();
		while (descriptionRs.next())
		{
			String description = descriptionRs.getString(1);
			if (description != null)
			{
				//unescap the html coding
				description = StringEscapeUtils.unescapeHtml(description);
				
				//tokenize this string
				//StringTokenizer tokens = new StringTokenizer(description, "\n\r");
				
				//int tokenNum = tokens.countTokens();
				
				addMap(StringUtils.countMatches(description, "\n") 
						+ StringUtils.countMatches(description, "\r"));
				
				/*
				System.out.println("String ->");
				System.out.println(description);
				System.out.println("num of lines ->");
				System.out.println(tokenNum == 1 ? 1: tokenNum - 1);
				*/
			}
			
		}
		
		writeDistributionToFile(bugDstFile);
		
		bugDstFile = null;
	}
	

	//write the distribution to file
	private void writeDistributionToFile(String dstFile) throws IOException
	{
		FileWriter fstream = new FileWriter(dstFile);
		
		BufferedWriter out = new BufferedWriter(fstream);
		
		System.out.println(distributionMap.size() + " different distributions");
		
		//heading distribution count
		out.write("# " + distributionMap.size() + "\n"); 
		
		//iteratively push the suffices into file
		
		Set<Integer> sizeSet = distributionMap.keySet();
		
		for (Integer size: sizeSet)
		{
			int num = distributionMap.get(size);
			
			out.write(size + "\t" + num + "\n");  
		}
		
		out.close();
		
	}
	
	private void loadBugid(int rowNumLimit) throws SQLException
	{
		//store bugid
		bugidSet = new HashSet<Integer>();
		
		//sql statement that counts the number of rows in comment given bugid
		String getBugidStatement = 
				"select distinct bugid from bugBody where bugid is not null";
		
		//prepared statement object for comment
		PreparedStatement getBugidps = null;
		
		try
		{
			//get the prepared statement
			getBugidps = db.getStreamPreparedStatement(getBugidStatement, 
					rowNumLimit);			
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		
		//retrive description
		ResultSet bugidRs = getBugidps.executeQuery();
		while (bugidRs.next())
		{
			int bugid = bugidRs.getInt(1);
		
			bugidSet.add(bugid);
		}

	}
	
	/*
	private void loadFileSuffix(String suffixFromFile) throws IOException
	{
		suffixSet = new HashSet<String>();
		
		FileInputStream fstream = new FileInputStream(suffixFromFile);
		DataInputStream in = new DataInputStream(fstream);
		BufferedReader br = new BufferedReader(new InputStreamReader(in));
		
		String line = null;
		
		//bypass the first comment line
		line = br.readLine();
		while ((line = br.readLine()) != null)   
		{
			suffixSet.add(line);
		}
		
		System.out.println(suffixSet);
	}
	*/
	
	private void getDescriptionReference(int rowNumLimit) throws SQLException, IOException
	{
		loadBugid(BUGID_ROW_LIMIT);
		fileReferencedSet = new HashSet<Integer>();
		bugidReferencedSet = new HashSet<Integer>();
		
		//bugid regex object
		String bugidRegex = "(\\W|^)+bug(\\s*|\\W)+(\\d{1,5})($|\\W)+?";
		
		Pattern bugidPattern = Pattern.compile(bugidRegex, Pattern.CASE_INSENSITIVE);
		
		Matcher bugidMatch = bugidPattern.matcher("");
		
		
		//file regex object
		//String fileRegex = "(/|\\.|^|\\W)(\\w+)[^/\\.\\w]*$+";
		String fileRegex = "^\\w+\\.\\w+$";
		
		Pattern filePattern = Pattern.compile(fileRegex, Pattern.CASE_INSENSITIVE);
		
		Matcher fileMatch = filePattern.matcher("");
		
		
		//sql statement that counts the number of rows in comment given bugid
		String countDescriptionStatement = 
				"select * from description where description is not null";
		
		//prepared statement object for comment
		PreparedStatement countDescriptionps = null;
		
		//sql statement that checks the existence of filename
		String checkFilenameStatement = 
				"select 1 from filename where filename = ?";
		
		//prepared statement object for comment
		PreparedStatement checkFilenamePs = null;
		
		//result set for filename existence check
		ResultSet filenameRs = null;
		
		try
		{
			//get the prepared statement
			countDescriptionps = db.getStreamPreparedStatement(countDescriptionStatement, 
					rowNumLimit);
			
			
			checkFilenamePs = db.getDynamicCheckPreparedStatement(checkFilenameStatement);
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		
		int fileReferenceCount = 0;
		int bugidReferenceCount = 0;
		
		//get the description content
		ResultSet descriptionRs = countDescriptionps.executeQuery();
		while (descriptionRs.next())
		{
			String description = descriptionRs.getString(2);
			
			String[] words = description.split("\\s");
			
			for (String word: words)
			{
				//match the suffix
				String[] filepath = word.split("/");
				
				if (filepath.length > 0)
				{
					String finalName = filepath[filepath.length - 1];
					
					fileMatch.reset(finalName);
					
					if (fileMatch.find())
					{
							
						checkFilenamePs.setString(1, finalName);
						filenameRs = checkFilenamePs.executeQuery();
						
						//filename exists
						if (filenameRs.next())
						{
							fileReferenceCount++;
							
							fileReferencedSet.add(descriptionRs.getInt(1));
							
							//System.out.println(filepath[filepath.length - 1]+ " " + word);
						}
					}
				}
				
				/*
				fileMatch.reset(word);
				while (fileMatch.find())
				{
					String suffix = fileMatch.group(fileMatch.groupCount());
					
					//lookup the suffices condictionary
					if (suffixSet.contains(suffix))
					{
						suffixReferenceCount++;
						
						System.out.println(suffix + " " + word);
					}
				}
				*/
				
			}
			
			//match the bugid
			bugidMatch.reset(description);
			while (bugidMatch.find())
			{				
				String idString = bugidMatch.group(bugidMatch.groupCount() - 1);
				
				if (idString != null)
				{
					//lookup the bugid condictionary
					int bugid = Integer.parseInt(idString);
				
					if (bugidSet.contains(bugid))
					{
						bugidReferenceCount++;
						
						bugidReferencedSet.add(descriptionRs.getInt(1));
								
						System.out.println(bugid + " " + bugidMatch.group(0));
					}
				}

			}
		}
		
		System.out.println(fileReferencedSet.size() + 
				" descriptions referenced " + fileReferenceCount + " files");
		
		System.out.println(bugidReferencedSet.size() + " referenced "
				+ bugidReferenceCount + " bugs");
		
	}
	
	private void getCommentEntitySize(int rowNumLimit, String commentDstFile) 
			throws SQLException, IOException
	{
		//instantiate the distribution hash map
		distributionMap = new HashMap<Integer, Integer>();
		
		//sql statement that counts the number of rows in comment given bugid
		String countCommentStatement = "select * from comment";
		
		//prepared statement object for comment
		PreparedStatement countCommentPs = null;
		
		try
		{
			//get the prepared statement
			countCommentPs = db.getStreamPreparedStatement(countCommentStatement, 
					rowNumLimit);			
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		
		int entityNum = 0;
		
		//retrive description
		ResultSet CommentRs = countCommentPs.executeQuery();
		while (CommentRs.next())
		{
			
			String what = CommentRs.getString(4);
			if (what != null)
			{
				//unescap the html coding
				what = StringEscapeUtils.unescapeHtml(what);
			
				//System.out.println(what);
				
				addMap(StringUtils.countMatches(what, "\n") 
						+ StringUtils.countMatches(what, "\r"));
		
			}
			
		}
		
		writeDistributionToFile(commentDstFile);
		
		commentDstFile = null;
	}
	
	private void getCommentReference(int rowNumLimit) throws SQLException, IOException
	{
		loadBugid(BUGID_ROW_LIMIT);
		fileReferencedSet = new HashSet<Integer>();
		bugidReferencedSet = new HashSet<Integer>();
		
		//bugid regex object
		String bugidRegex = "(\\W|^)+bug(\\s*|\\W)+(\\d{1,5})($|\\W)+?";
		
		Pattern bugidPattern = Pattern.compile(bugidRegex, Pattern.CASE_INSENSITIVE);
		
		Matcher bugidMatch = bugidPattern.matcher("");
		
		
		//file regex object
		//String fileRegex = "(/|\\.|^|\\W)(\\w+)[^/\\.\\w]*$+";
		String fileRegex = "^\\w+\\.\\w+$";
		
		Pattern filePattern = Pattern.compile(fileRegex, Pattern.CASE_INSENSITIVE);
		
		Matcher fileMatch = filePattern.matcher("");
		
		
		//sql statement that counts the number of rows in comment given bugid
		String countCommentStatement = "select * from comment where wha is not null";
		
		//prepared statement object for comment
		PreparedStatement countCommentps = null;
		
		//sql statement that checks the existence of filename
		String checkFilenameStatement = 
				"select 1 from filename where filename = ?";
		
		//prepared statement object for comment
		PreparedStatement checkFilenamePs = null;
		
		//result set for filename existence check
		ResultSet filenameRs = null;
		
		try
		{
			//get the prepared statement
			countCommentps = db.getStreamPreparedStatement(countCommentStatement, 
					rowNumLimit);
			
			
			checkFilenamePs = db.getDynamicCheckPreparedStatement(checkFilenameStatement);
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		
		int fileReferenceCount = 0;
		int bugidReferenceCount = 0;
		
		//get the description content
		ResultSet commentRs = countCommentps.executeQuery();
		while (commentRs.next())
		{
			String what = commentRs.getString(4);
			
			String[] words = what.split("\\s");
			
			for (String word: words)
			{
				//match the suffix
				String[] filepath = word.split("/");
				
				if (filepath.length > 0)
				{
					String finalName = filepath[filepath.length - 1];
					
					fileMatch.reset(finalName);
					
					if (fileMatch.find())
					{
							
						checkFilenamePs.setString(1, finalName);
						filenameRs = checkFilenamePs.executeQuery();
						
						//filename exists
						if (filenameRs.next())
						{
							fileReferenceCount++;
							
							fileReferencedSet.add(commentRs.getInt(1));
							
							//System.out.println(filepath[filepath.length - 1]+ " " + word);
						}
					}
				}
				/*
				fileMatch.reset(word);
				while (fileMatch.find())
				{
					String suffix = fileMatch.group(fileMatch.groupCount());
					
					//lookup the suffices condictionary
					if (suffixSet.contains(suffix))
					{
						suffixReferenceCount++;
						
						System.out.println(suffix + " " + word);
					}
				}
				*/
				
			}
			
			//match the bugid
			bugidMatch.reset(what);
			while (bugidMatch.find())
			{				
				String idString = bugidMatch.group(bugidMatch.groupCount() - 1);
				
				if (idString != null)
				{
					//lookup the bugid condictionary
					int bugid = Integer.parseInt(idString);
				
					if (bugidSet.contains(bugid))
					{
						bugidReferenceCount++;
						
						bugidReferencedSet.add(commentRs.getInt(1));
								
						System.out.println(bugid + " " + bugidMatch.group(0));
					}
				}

			}
		}
		
		System.out.println(fileReferencedSet.size() + 
				" comments referenced " + fileReferenceCount + " files");
		
		System.out.println(bugidReferencedSet.size() + " comments referenced "
				+ bugidReferenceCount + " bugs");
		
	}
	
	
	public static void main(String args[]) throws ClassNotFoundException, 
	IllegalAccessException, SQLException, IOException
	{
		GetBugStatistics bugStatics = new GetBugStatistics();
		
		//bugStatics.getBugSubEntityNum(GetBugStatistics.BUGBODY_ROW_LIMIT,
				//"data/bugStatistics.txt");
		
		//bugStatics.getDescriptionSize(GetBugStatistics.DESCRIPTION_ROW_LIMIT, 
				//"data/descriptionStatistics.txt");
		
		//bugStatics.getDescriptionReference(GetBugStatistics.BUGID_ROW_LIMIT);
		
		
		//bugStatics.getCommentEntitySize(GetBugStatistics.COMMENT_ROW_LIMIT,
				//"data/commentStatistics.txt");
		
		//bugStatics.getCommentReference(GetBugStatistics.COMMENT_ROW_LIMIT);
		
		/*
		//bugid regex object
		String bugidRegex = "(\\s|^)+bug(\\s*|\\W)+(\\d{1,5})($|\\s)+?";
		
		Pattern bugidPattern = Pattern.compile(bugidRegex, Pattern.CASE_INSENSITIVE);
		
		Matcher bugidMatch = bugidPattern.matcher("aa bug 1123  bug #1123");

		while (bugidMatch.find())
		{				
			String idString = bugidMatch.group(bugidMatch.groupCount()-1);
		
			System.out.println(idString);
		}
		*/
	
	}
	
}
